<?php

class spSql
{
	private $pdo = null;

	private $result;
	private $last_query = "";

	public function __construct(  $host, $database, $username, $password )
	{
		$this->connect( $host, $database, $username, $password );
	}
	
	public function connect( $host, $database, $username, $password )
	{
		$dsn = "mysql:host={$host};dbname={$database}";
		$user = $username;

		try {
			$this->pdo = new PDO( 
				$dsn, 
				$user, 
				$password,
				array(
					1002 => 'SET NAMES utf8',
					PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
					PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
				)
			);
			$this->pdo->setAttribute( PDO::ATTR_STRINGIFY_FETCHES, true );
			
		} catch (PDOException $e) {
			echo 'ERROR: spSql -> Connection failed: '.$e->getMessage();
		}
	}
	
	function tableExists( $table )
	{
		$input = $this->query( "SHOW TABLES LIKE '{$table}'" );
		return ($input!=false);
	}

	function createTable( $table, $arParam )
	{
		$param = spImplode( ",", $arParam );
		
		$query = "CREATE TABLE IF NOT EXISTS {$table} ({$param}) ENGINE=InnoDB";
		//echo $query."<br/>";
		try {
			$statement = $this->pdo->prepare( $query );
			if ( !$statement )
				return false;
				
			$statement->execute();
		} catch (PDOException $e) {
			echo 'ERROR: spSql::createTable failed: '.$e->getMessage()."<br/>";
		}

		return true;
	}	
	
	public function query( $query )
	{
		try {
			$statement = $this->pdo->prepare( $query );
			if ( !$statement )
				return false;
			
			$statement->execute();
			return $statement->fetchAll( PDO::FETCH_ASSOC );
		} catch (PDOException $e) {
			echo 'ERROR: spSql::query failed: '.$e->getMessage()."<br/>";
			return false;
		}
	}
	
	public function insert( $table, $arColValues )
	{
		$arCols = array();
		$arValues = array();
		$arQs = array();
		foreach ( $arColValues as $col => $value )
		{
			$arCols[] = $col;
			$arValues[] = $value;
			$arQs[] = "?";
		}
		
		$cols = spImplode( ",", $arCols );
		$qs = spImplode( ",", $arQs );
	
		try {
			$query = "INSERT INTO {$table} ({$cols}) VALUES ({$qs})";
			$statement = $this->pdo->prepare( $query );
			$statement->execute( $arValues );		
		} catch (PDOException $e) {
			echo 'ERROR: spSql::insert failed: '.$e->getMessage()."<br/>";
			return false;
		}
		
		return true;
	}

	public function lastInsertId()
	{
		return $this->pdo->lastInsertId();
	}
	
	public function fetchAll( $query )
	{
		$result = $this->query($query);
		if ( $result!==false )
		{
			$func = "mysql_fetch_{$type}";
			$output = array();
			while ( $row = $func($result) )
			{
				$output[] = $row;
			}
			return $output;
		}
		return false;
	}
	
	public function fetchRow( $query, $type='assoc')
	{
		$result = $this->query($query);
		if ( $result!=false )
		{
			$func = "mysql_fetch_{$type}";
			return($func($result));
		}
		return false;
	}

	public function errorInfo()
	{
		$html = "";
		$html.= "<div class='spsql_error'>";
		$html.= "QUERY: ".$this->last_query."<br/>".mysql_errno()." : ".mysql_error();
		$html.= "</div>";
		return $html;
	}

	public function columnExists( $table, $field )
	{
		$this->last_query = "SELECT {$field} FROM {$table} LIMIT 0";
		return ($this->query($this->last_query)===false?false:true);
	}

	// defaults: $field, $type, $null="YES", $key="", $default=null, $extra=""
	public function createQueryStringFromField( $field )
	{
		$s = $field["field"]." ".$field["type"];

		if ( isset($field["null"]) )
		{
			$s.= " ".$field["null"]=="NO"?"NOT NULL":"";
		}

		if ( isset($field["extra"]) )
		{
			$s.= " ".$field["extra"];
		}

		return $s;
	}

	public function addColumn( $table, $field )
	{
		$f = $this->createQueryStringFromField( $field );
		$this->last_query = "ALTER TABLE {$table} ADD {$f}";
		return ($this->query($this->last_query)===false?false:true);
	}

	public function changeColumn( $table, $field )
	{
		$f = $this->createQueryStringFromField( $field );
		$this->last_query = "ALTER TABLE {$table} CHANGE {$field['field']} {$f}";
		return ($this->query($this->last_query)===false?false:true);
	}

	public function getFields( $table )
	{
		$this->last_query = "SHOW FIELDS FROM {$table}";
		$a = $this->fetchAll( $this->last_query );
		if ( $a===false )
			return false;

		return $a;
	}

	public function isColumnType( $table, $field )
	{
		$this->last_query = "SHOW FIELDS FROM {$table} WHERE FIELD='{$field['field']}'";
		$a = $this->fetchRow( $this->last_query );
		if ( $a===false )
			return false;

		if ( $a["Type"]!=$field["type"] )
			return false;

		$extra = isset($field["extra"])?$field["extra"]:"";
		if ( strcasecmp($a["Extra"],$extra) )
		{
			return false;
		}

		return true;
	}

	public function updateRow( $primary_key, $id, $table, $arColValues )
	{
		$updateme = "";

		foreach ( $arColValues as $col )
		{
			$updateme.= $col["col"]."='".$col["value"]."',";
		}

		// remove last comma
		$updateme = substr($updateme,0,-1);

		$this->last_query = "UPDATE {$table} SET {$updateme} WHERE {$primary_key}={$id}";
		return ($this->query($this->last_query)===false?false:true);
	}

	public function removeColumn( $table, $col )
	{
		$this->last_query = "ALTER TABLE {$table} DROP {$col}";
		return ($this->query($this->last_query)===false?false:true);
	}

	public function getLastId()
	{
		return @mysql_insert_id( $this->link_identifier );
	}
}

?>